from django.shortcuts import render
from django.http import HttpResponse
from .models import *

from django.db.models import Count, Max, Sum, Avg  # 用于分组、聚合查询
from django.db.models import F, Q  # 用于F、Q查询

# Create your views here.


def app01index(request):
    return HttpResponse("app01 index")


def getdatas(request):
    # 一、查询数据
        # 1、all方法
    user = StoreUser.objects.all()
    for one in user:
        print(one.username)
    print("--------------------")

        # 2、get方法
    user = StoreUser.objects.get(username="shihan")
    print(user.nick_name)
    print("--------------------")

        # 3、filter方法，返回的数据为queryset
    user = StoreUser.objects.filter(username="xiaoyu")
    print(user)
    print(user.first())
    print(user.first().nick_name)
    print("--------------------")

        # 4、order_by 方法:升序、降序
    user = StoreUser.objects.order_by("username")
    print(user)
    for one in user:
        print(one.nick_name)

    user = StoreUser.objects.order_by("-id")
    print(user)
    for one in user:
        print(one.nick_name)
    print(user.last().nick_name)
    print("--------------------")

        # 5、value方法
    user = StoreUser.objects.values()
    print(user)
    user = StoreUser.objects.values("nick_name", "age")
    print(user)
    print("--------------------")

        # 6、exits方法，判断某个数据是否存在
    flag = StoreUser.objects.filter(gender="女").exists()
    print(flag)
    flag = StoreUser.objects.filter(age=22).exists()
    print(flag)
    print("--------------------")

        # 7、双下划线
    # __gt：大于
    # __gte：大于等于
    # __lt：小于
    # __lte：小于等于
    # __contains：模糊查询，类似于like。包含
    # __in：范围查询  id__in[1,3]，id为1、3的数据
    # __startswith：以什么开头
    # __endswith：以什么结尾
    # __range：范围查询 id__range[1,3]，id从1到3的数据

    # 年龄大于10的人
    user = StoreUser.objects.filter(age__gt=10)
    print(user)
    print(user.last().nick_name)
    print("--------------------")

        # 8、聚合aggregate查询Max、Min、Sum、Avg、Count

    # 统计大于10岁的人数

    data = StoreUser.objects.filter(age__gt=10).aggregate(Count("id"))  # {'id__count':2}
    data1 = StoreUser.objects.filter(age__lt=30).aggregate(num=Count("username"))  # {'num':2}
    data2 = StoreUser.objects.aggregate(Max("age"))
    print(data, data1, data2)
    print("--------------------")

        # 9、分组annotate查询，需与values配合使用，values内放置分组的依据
        #     (我认为分组好似删选后的聚合)

    # 查询男女人数
    data = StoreUser.objects.all().values("gender").annotate(Count("gender"))
    data1 = StoreUser.objects.all().values("gender").annotate(Avg("age"))
    data2 = StoreUser.objects.values("gender").annotate(Sum("age"))
    print(data)
    print(data1)
    print(data2)
    print("--------------------")


        # 10、F查询，查询数据的值，可用于两个字段的比较
    # data = StoreUser.objects.get(id=1)
    # id = F(data.id)
    # 查询年龄大于id+5的数据
    data = StoreUser.objects.values("nick_name").filter(age__gt=F("id")+5)
    data1 = StoreUser.objects.filter(age__gt=F("id")+5).values("nick_name")
    print(data, data1)
    print("--------------------")

        # 11、Q查询，逻辑查询
    # not: ~   and：&   or：|

    # 选出男生<20岁的
    data = StoreUser.objects.filter(gender="男", age__lt=20)
    data1 = StoreUser.objects.filter(Q(gender="男"), Q(age__lt=20))
    data2 = StoreUser.objects.filter(Q(gender="男") & Q(age__lt=20))
    # data data1 data2结果一样
    print(data)
    print(data1)
    print(data2)
    print("--------------------")

        # 12、分页，类似于切片
    data = StoreUser.objects.all()[1:2]  # 左闭右开
    print(data)
    print("--------------------")
    return HttpResponse("查询数据")


def modifydatas(request):
    # 修改数据，直接修改
    # user = StoreUser.objects.get(age=4)
    # user.nick_name = "周诗涵"
    # user.save()
    #
    # # update方法
    # flag = StoreUser.objects.filter(age__gt=6).update(address = "湖南省武冈市安乐乡新泽村")
    # print(flag)  # 返回的是修改的数据条数

    # GoodsType.objects.filter(id=1).update(t_store=Store.objects.filter(id__gt=1).first())
    # gt = GoodsType.objects.get(id=1)

    return HttpResponse("修改数据")


def deletedatas(request):
    # 删除数据，在对象和queryset后面都可以使用
    # flag = StoreUser.objects.filter(age__lt=5).delete()
    # print(flag)  # (1, {'app01.StoreUser': 1})
    # flag1 = StoreUser.objects.get(id=1).delete()
    # print(flag1)  # (1, {'app01.StoreUser': 1})
    flag2 = StoreUser.objects.all().delete()
    print(flag2)
    return HttpResponse("删除数据")


def adddatas(request):
    # store1 = Store()
    # store1.name = "周氏烧烤"
    # store1.description = "美味不可少"
    # store1.store_user = StoreUser.objects.get(id=1)
    # store1.save()
    #
    # Store.objects.create(
    #     name= "陈赫火锅",
    #     description = "明星火锅",
    #     store_user= StoreUser.objects.filter(id=2).first()
    # )

    # Store.objects.create(
    #     name= "孙艺洲烧烤",
    #     description = "明星烧烤",
    #     store_user= StoreUser.objects.filter(id=3).first(),
    # )

    # 增加商品和商品类型的数据
    # GoodsType.objects.create(
    #     t_name="水果",
    #     t_description="补充维生素",
    # )
    # GoodsType.objects.create(
    #     t_name="蔬菜",
    #     t_description="绿色有机素菜",
    # )
    # gt = GoodsType(t_name="肉", t_description="补充蛋白质")
    # gt.save()

    # Goods.objects.create(
    #     g_name="苹果",
    #     g_price=12,
    #     goods_type = GoodsType.objects.get(t_name="水果"),
    # )
    #
    # g = Goods()
    # g.g_name = "猪肉"
    # g.g_price = 28
    # g.goods_type = GoodsType.objects.filter(t_name="肉").first()
    # g.save()
    # #
    # g1 = Goods(
    #     g_name="白菜",
    #     g_price=2,
    #     goods_type=GoodsType.objects.get(t_name="蔬菜"),
    # )
    # g1.save()

    # 增加商铺和商品类型的关系
    # gt = GoodsType.objects.get(id=1)
    # gt.t_store.add(Store.objects.get(id=1))
    # gt.t_store.add(Store.objects.get(id=2))

    # gt = GoodsType.objects.get(id=2)
    # gt.t_store.add(Store.objects.get(id=2))
    # gt.t_store.add(Store.objects.get(id=3))
    #
    # gt = GoodsType.objects.get(id=3)
    # gt.t_store.add(Store.objects.get(id=1))
    # gt.t_store.add(Store.objects.get(id=3))



    return HttpResponse("增加数据")


def check(request):
    # 1、查询周氏烧烤店的老板
    # store = Store.objects.get(name="周氏烧烤")
    # store_user = store.store_user.nick_name
    # print(store_user)
    # print("-----------------")
    # # 1、查询晓宇的店铺
    # storeuser = StoreUser.objects.filter(nick_name="晓宇").first()
    # store_name = Store.objects.get(store_user=storeuser).name
    # print(store_name)
    #
    # return HttpResponse("一对一数据查询")


    # 1、查询有哪些水果
    # t_fruits = GoodsType.objects.get(t_name="水果")
    # fruits = Goods.objects.filter(goods_type=t_fruits)
    # for fruit in fruits:
    #     print(fruit.g_name)
    #
    # # 2、查询牛肉的类型
    # gt = Goods.objects.get(g_name="牛肉").goods_type.t_name
    # print(gt)

    # return HttpResponse("一对多的数据查询")

    # 1、查询卖水果的店铺
    # gt = GoodsType.objects.get(t_name="水果")
    # gt_s = gt.t_store.all()
    # for gs in gt_s:
    #     print(gs.name)

    # 2、查询孙艺洲烧烤卖商品的类型
    store = Store.objects.get(name="孙艺洲烧烤")
    gts = store.goodstype_set.all()
    print(gts)
    for gt in gts:
        print(gt.t_name)


    return HttpResponse("多对多的数据查询")